<?php
require_once('config.inc.php');
require_once('WallSocket.php');


define("QUERY_SELECT_TABLE_MAP", "SELECT name FROM sqlite_master WHERE type='table' and name='map'");
define("QUERY_CREATE_TABLE_MAP", "CREATE TABLE map (id INTEGER PRIMARY KEY, floor int(2), rack char(1),patchPanel char(1),socket int(2), coordX int(2),coordY int(2),used int(1),user char(20), data char(30))");
define("QUERY_INSERT_SOCKET", "INSERT INTO map VALUES (null,:floor,:rack,:patchPanel,:socket,:coordX,:coordY,:used,:user,:date)");
define("QUERY_SELECT_ALL_OF_FLOOR", 'SELECT * FROM map WHERE floor=:floor AND used=1');
define("QUERY_SELECT_COUNT_FLOORS", 'SELECT COUNT(*) FROM (SELECT DISTINCT(floor) FROM map) AS floors');
define("QUERY_UPDATE_WALL_SOCKET",'UPDATE map SET coordX=:coordX,coordY=:coordY, used=:used, user=:user, data=:date WHERE id=:id');

class MyDB {
    private $connection = null;    
 
    
    public function __construct(){
        //initilize connection
        $this->connection = new PDO('sqlite:php/networkmap.db');

        $stmt = $this->connection->prepare(QUERY_SELECT_TABLE_MAP);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if(!$row){
            $this->init();
        }
        $stmt->closeCursor();
    }
    
    
    public function init(){
        
        $stmt_create = $this->connection->prepare(QUERY_CREATE_TABLE_MAP);
        $stmt_create->execute();
        $stmt_create->closeCursor();
    }
    
    public function disconnect(){
	//$this->connection->close();
    }
    
    public function countFloors(){
        $stmt = $this->connection->prepare(QUERY_SELECT_COUNT_FLOORS);
        $stmt->execute();
        $numberOfFloors = $stmt->fetchColumn(0);
        return $numberOfFloors;
    }
    
    public function selectAll($p) {
        $stmt = $this->connection->prepare(QUERY_SELECT_ALL_OF_FLOOR);
        $stmt->bindParam(':floor', $p, PDO::PARAM_INT,1);
        $stmt->execute();

        $floorSockets = array();
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $newSocket = new WallSocket();    

            $newSocket->setId($row['id']);
            $newSocket->setFloor($row['floor']);
            $newSocket->setRack($row['rack']);
            $newSocket->setPatchPanel($row['patchPanel']);
            $newSocket->setSocket($row['socket']);
            $newSocket->setCoordX($row['coordX']);
            $newSocket->setCoordY($row['coordY']); 
            
            //append current socket to floorSockets array
            $floorSockets[] = $newSocket;                        
        }
        
        return $floorSockets;
    }


    
    public function updateSocket($ws){
        //check if id is unique or unique only on the same floor. if unique for floor only
        //it should be added floorid in the where clause 
        $stmt = $this->connection->prepare(QUERY_UPDATE_WALL_SOCKET);
        $stmt->bindParam(':coordX', $ws->coordX, PDO::PARAM_INT);
        $stmt->bindParam(':coordY', $ws->coordY, PDO::PARAM_INT);
        $stmt->bindParam(':id', $ws->id, PDO::PARAM_INT);
        $stmt->bindParam(':used', $ws->used, PDO::PARAM_INT,1);
        $stmt->bindValue(':user', $_SESSION['username']);
        $stmt->bindValue(':date', $ws->date);
        $updated = $stmt->execute();
         
        if(!$updated){
            echo "unable to update";
        }
    }


    public function insertPatchPanel($sArray){
        foreach($sArray as $value){
        $stmt_insert = $this->connection->prepare(QUERY_INSERT_SOCKET);
        $stmt_insert->bindParam(":floor",$value->floor, PDO::PARAM_INT, 0);
        $stmt_insert->bindParam(":rack",$value->rack, PDO::PARAM_STR, 1);
        $stmt_insert->bindParam(":patchPanel", $value->patchPanel, PDO::PARAM_STR, 1);
        $stmt_insert->bindParam(":socket", $value->socket, PDO::PARAM_INT);
        $stmt_insert->bindParam(":coordX", $value->coordX);
        $stmt_insert->bindParam(":coordY", $value->coordY);
        $stmt_insert->bindParam(":used", $value->used);
        $stmt_insert->bindValue(":user", $_SESSION['username']);
        $stmt_insert->bindValue(":date",$value->date);
        $stmt_insert->execute();
        $stmt_insert->closeCursor();
     
        }
}

public function selectFloors(){
    $sql="SELECT DISTINCT(floor) FROM map ORDER BY floor";

    $stmt = $this->connection->prepare($sql);
    $updated = $stmt->execute();
    
       $allFloors = array();
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $allFloors[] = $row['floor'];                        
        }
        
        return $allFloors;

}

public function selectRack($fl){
    $sql="SELECT DISTINCT(rack) FROM map WHERE floor=:floor ORDER BY rack";
   
    $stmt = $this->connection->prepare($sql);
    $stmt->bindParam(":floor",$fl, PDO::PARAM_INT, 1);
    $updated = $stmt->execute();
    
       $allRacks = array();
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $allRacks[] = $row['rack'];                        
        }
        
        return $allRacks;
}

public function selectPP($fl,$rk){
    $sql="SELECT DISTINCT(patchPanel) FROM map WHERE floor=:floor AND rack=:rack ORDER BY patchPanel";
    $stmt = $this->connection->prepare($sql);
    $stmt->bindParam(":floor",$fl, PDO::PARAM_INT, 1);
    $stmt->bindParam(":rack",$rk, PDO::PARAM_STR, 1);
    $updated = $stmt->execute();
    
       $allPP = array();
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $allPP[] = $row['patchPanel'];                        
        }
        return $allPP;
}


   
    public function selectSockets($fl,$rk,$pp){
    $sql="SELECT id,socket,coordX,coordY,used FROM map WHERE floor=:floor AND rack=:rack AND patchPanel=:patchPanel ORDER BY socket";
    $stmt = $this->connection->prepare($sql);
    $stmt->bindParam(":floor",$fl, PDO::PARAM_INT, 1);
    $stmt->bindParam(":rack",$rk, PDO::PARAM_STR, 1);
    $stmt->bindParam(":patchPanel",$pp, PDO::PARAM_STR, 1);
    $updated = $stmt->execute();
    
       $allSockets = array();
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $socket = new WallSocket();    

            $socket->setId($row['id']);
            $socket->setSocket($row['socket']);
            $socket->setFloor($fl);
            $socket->setRack($rk);
            $socket->setPatchPanel($pp);
            $socket->setCoordX($row['coordX']);
            $socket->setCoordY($row['coordY']);
            $socket->setUsed($row['used']); 
            
            //append current socket to allSockets array
            $allSockets[] = $socket;                        
        }   
        return $allSockets;
    }
}